#include "SqlDatabaseManager.h"
#include <QSqlDatabase>
#include <QFile>
#include <QDir>
#include <QList>

#include "XXlogger.h"
#include <QSqlQuery>
#include <QDebug>
#include <QSqlError>
#include <QSqlRecord>

#include <QSqlDriver>
#include <functional>
#include <mutex>
#include <QMetaProperty>

XX_KEY_C(SqlDatabaseManager,kTableMainKeyType,"INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL");
XX_KEY_C(SqlDatabaseManager,kTableName,"db_table_name");
uint SqlDatabaseManager::_version = 0;

uint SqlDatabaseManager::getGlobalVersion(){
    return _version;
}
void SqlDatabaseManager::setGlobalVersion(uint version){
    static std::once_flag flag;
    std::call_once(flag,[&]{
        _version = version;
    });
}
void SqlDatabaseManager::init(const QString &path, const QVariantMap &tableInfo){
    SqlDatabaseManager db;
    db.open(path);
    for (auto iter=tableInfo.begin(); iter!=tableInfo.end(); ++iter) {
        QString tableName = iter.key();
        if(iter.value().canConvert(QMetaType::QVariantMap)){
            QVariantMap kt = iter.value().toMap();
            db.createTable(tableName,kt);
        }
        else if(iter.value().canConvert(QMetaType::QStringList)){
            QStringList ks = iter.value().toStringList();
            db.createTable(ks,tableName);
        }
        else{

        }
    }
    db.close();
}
void SqlDatabaseManager::init(const QString &path, QMap<QString,QMetaObject> tableNameTometaObject){
    QVariantMap tableInfo;
    for (auto iter=tableNameTometaObject.begin(); iter!=tableNameTometaObject.end(); iter++) {
        QVariantMap kt;
        int count = iter.value().propertyCount();
        for (int index=0; index<count; index++) {
            auto property = iter.value().property(index);
            if(!property.isUser()){
                continue;
            }
            switch (property.type()) {
                case QVariant::Type::String:
                    kt[property.name()] = "TEXT";
                    break;
                case QVariant::Type::Int:
                    kt[property.name()] = "INTEGER";
                    break;
                case QVariant::Type::LongLong:
                    kt[property.name()] = "BIGINT";
                    break;
                default:
                    continue;
            }
        }
        if(kt.isEmpty()){
            continue;
        }
        tableInfo[iter.key()] = kt;
    }
    init(path,tableInfo);
}
void SqlDatabaseManager::init(const QString &path, QList<QMetaObject> metaObjects){
    QMap<QString,QMetaObject> tableNameToObject;
    foreach (auto metaObject, metaObjects) {
        int index = metaObject.indexOfClassInfo(kTableName);
        if(index<0){
            continue;
        }
        QString tableName = metaObject.classInfo(index).value();
        tableNameToObject[tableName] = metaObject;
    }
    init(path,tableNameToObject);
}

SqlDatabaseManager::SqlDatabaseManager(QObject *parent)
    : QObject(parent)
    , _isOpen(false){

}
SqlDatabaseManager::~SqlDatabaseManager(){
    if(_isOpen){
        this->close();
    }
}

bool SqlDatabaseManager::open(const QString &path, bool create){
    if(_isOpen){
        xxErr("can not open database again.");
        return false;
    }
    _isOpen = true;

    // 设置数据库的name和路径，当没有数据库文件时自动创建一个
	QFileInfo fileInfo(path);
    if (!fileInfo.dir().exists() || !fileInfo.exists()) {
        if(create){
            QDir().mkpath(fileInfo.absolutePath());
        }
        else{
            _isOpen = false;
            return false;
        }
	}

    QString fileName = fileInfo.fileName();
    _name = fileName.replace(".","_");
    if(QSqlDatabase::contains(_name)){
        _database = QSqlDatabase::database(_name);
    }
    else{
        _database = QSqlDatabase::addDatabase("QSQLITE", _name);
        _database.setDatabaseName(path);
    }


    // 打开数据库
    if(!_database.open()){
        xxErr("failure to open database. path:%s error:%s", path.toLocal8Bit().data(), _database.lastError().text().toLocal8Bit().data());
        _isOpen = false;
        return false;
    }

    //XXDebug("open database succeed. path:%s", path.toLocal8Bit().data());
    return true;
}
bool SqlDatabaseManager::createTable(const QString &tableName, const QVariantMap &keyAndType){
    if(!_isOpen || 0 == keyAndType.count() || tableName.isEmpty()){
        xxErr("database not open or invalid arg.");
        return false;
    }

    if(tableExisted(tableName)){
        xxErr("table is existed. tableName:%s", tableName.toLocal8Bit().data());
        return false;
    }

    QVariantMap kt = keyAndType;
    if(!kt.contains("id")){
        kt["id"] = kTableMainKeyType;
    }

    QString ktString;
    auto iter = kt.constBegin();
    for(; kt.constEnd()!=iter; iter++){
        ktString += iter.key() + " " + iter.value().toString();
        if(iter+1 != kt.constEnd()){
            ktString += ",";
        }
    }

    QString sqlString = QString("create table %1 (%2);").arg(tableName).arg(ktString);
    QSqlQuery sqlQuery(_database);
    if(!sqlQuery.exec(sqlString)){
        xxErr("create table failure. tableName:%s sql:%s error:%s",
                tableName.toLocal8Bit().data(),
                sqlString.toLocal8Bit().data(),
                sqlQuery.lastError().text().toLocal8Bit().data());
        return false;
    }

    //XXDebug("create table succeed. tableName:%s", tableName.toLocal8Bit().data());
    return true;
}
bool SqlDatabaseManager::createTable(const QMap<QString,QString> &keyAndType, const QString &tableName){
    if(!_isOpen || 0 == keyAndType.count() || tableName.isEmpty()){
        xxErr("database not open or invalid arg.");
        return false;
    }

    if(tableExisted(tableName)){
        xxErr("table is existed. tableName:%s", tableName.toLocal8Bit().data());
        return false;
    }

    QString ktString;
    QMap<QString,QString>::const_iterator iter = keyAndType.constBegin();
    for(; keyAndType.constEnd()!=iter; iter++){
        ktString += iter.key() + " " + iter.value();
        if(iter+1 != keyAndType.constEnd()){
            ktString += ",";
        }
    }

    QString sqlString = QString("create table %1 (%2);").arg(tableName).arg(ktString);
    QSqlQuery sqlQuery(_database);
    if(!sqlQuery.exec(sqlString)){
        xxErr("create table failure. tableName:%s sql:%s error:%s",
                tableName.toLocal8Bit().data(),
                sqlString.toLocal8Bit().data(),
                sqlQuery.lastError().text().toLocal8Bit().data());
        return false;
    }

    //XXDebug("create table succeed. tableName:%s", tableName.toLocal8Bit().data());
    return true;
}
bool SqlDatabaseManager::createTable(const QStringList &textKey, const QString &tableName){
    QMap<QString,QString> keyAndType;
    foreach (QString key, textKey) {
        keyAndType[key] = "text";
    }
    keyAndType["id"] = kTableMainKeyType;
    return createTable(keyAndType,tableName);
}
bool SqlDatabaseManager::tableExisted(const QString &tableName){
    if(!_isOpen || tableName.isEmpty()){
        xxErr("database not open or invalid arg.");
        return false;
    }

    QString sqlString = QString("select * from %1;").arg(tableName);
    QSqlQuery sqlQuery(_database);
    if(!sqlQuery.exec(sqlString)){
        return false;
    }
    return true;
}

bool SqlDatabaseManager::keyExisted(const QString &key, const QString &tableName){
    if(!_isOpen || key.isEmpty() || tableName.isEmpty()){
        xxErr("database not open or invalid arg.");
        return false;
    }

    QString sqlString = QString("select * from %1;").arg(tableName);
    QSqlQuery sqlQuery(_database);
    if (!sqlQuery.exec(sqlString)){
        xxErrStr("table not exist. tableName:"+tableName);
        return false;
    }

    QSqlRecord record = sqlQuery.record();
    int index = record.indexOf(key);
    if(-1==index){
        return false;
    }

    return true;
}
bool SqlDatabaseManager::addKey2Table(const QString &key, const QString &type, const QString &tableName){
    if(!_isOpen || key.isEmpty() || type.isEmpty() || tableName.isEmpty()){
        xxErr("database not open or invalid arg.");
        return false;
    }

    if(keyExisted(key, tableName)){
        xxErr("key is existed. key:%s tableName:%s",
                key.toLocal8Bit().data(),
                tableName.toLocal8Bit().data());
        return false;
    }

    QString sqlString = QString("alter table %1 add %2 %3;").arg(tableName).arg(key).arg(type);
    QSqlQuery sqlQuery(_database);
    if(!sqlQuery.exec(sqlString)){
        xxErr("add key failure. key:%s type:%s tableName:%s sql:%s error:%s",
                key.toLocal8Bit().data(),
                type.toLocal8Bit().data(),
                tableName.toLocal8Bit().data(),
                sqlString.toLocal8Bit().data(),
                sqlQuery.lastError().text().toLocal8Bit().data());
        return false;
    }

    //XXDebug("add key succeed. key:%s type:%s tableName:%s",
    //        key.toLocal8Bit().data(),
    //        type.toLocal8Bit().data(),
    //        tableName.toLocal8Bit().data());
    return true;
}
bool SqlDatabaseManager::addKeys2Table(const QVariantMap &keyAndType, const QString &tableName){
    _database.driver()->beginTransaction();
    bool ret = false;
    for (auto iter=keyAndType.cbegin(); iter!=keyAndType.cend(); iter++) {
        ret = addKey2Table(iter.key(),iter.value().toString(),tableName);
        if(!ret){
            break;
        }
    }
    if(ret){
        _database.driver()->commitTransaction();
    }
    else{
        _database.driver()->rollbackTransaction();
    }
    return ret;
}
bool SqlDatabaseManager::addKeys2Table(const QMap<QString,QString> &keyAndType, const QString &tableName){
    _database.driver()->beginTransaction();
    bool ret = false;
    for (auto iter=keyAndType.cbegin(); iter!=keyAndType.cend(); iter++) {
        ret = addKey2Table(iter.key(),iter.value(),tableName);
        if(!ret){
            break;
        }
    }
    if(ret){
        _database.driver()->commitTransaction();
    }
    else{
        _database.driver()->rollbackTransaction();
    }
    return ret;
}
bool SqlDatabaseManager::removeKeyAtTable(const QString &key, const QString &tableName){
#if 0
    if(!_isOpen || key.isEmpty() || tableName.isEmpty()){
        xxErr("database not open or invalid arg.");
        return false;
    }

    QString sqlString = QString("alter table %1 DROP column %2;").arg(tableName).arg(key);
    QSqlQuery sqlQuery(_database);
    if(!sqlQuery.exec(sqlString)){
        xxErr("remove key failure. key:%s tableName:%s sql:%s error:%s",
                key.toLocal8Bit().data(),
                tableName.toLocal8Bit().data(),
                sqlString.toLocal8Bit().data(),
                sqlQuery.lastError().text().toLocal8Bit().data());
        return false;
    }

    //XXDebug("remove key succeed. key:%s type:%s tableName:%s",
    //        key.toLocal8Bit().data(),
    //        tableName.toLocal8Bit().data());
#endif
    return false;
}
bool SqlDatabaseManager::removeMapAtTable(const QMap<QString, QVariant> &equalTo, const QString &tableName) {
	QString eqString;
	for (auto iter = equalTo.constBegin(); iter != equalTo.constEnd(); iter++) {
		eqString += iter.key();
		eqString += "=?";
		if (iter + 1 != equalTo.constEnd()) {
			eqString += " AND ";
		}
	}

    QString sqlString = eqString.isEmpty()?QString("DELETE FROM %1;").arg(tableName):QString("DELETE FROM %1 WHERE %2;").arg(tableName).arg(eqString);
	QSqlQuery sqlQuery(_database);
	sqlQuery.prepare(sqlString);
	for (auto iter = equalTo.constBegin(); iter != equalTo.constEnd(); iter++) {
		sqlQuery.addBindValue(iter.value());
	}

	if (!sqlQuery.exec()) {
        xxErr("delete map failure. tableName:%s sql:%s error:%s",
			tableName.toLocal8Bit().data(),
			sqlString.toLocal8Bit().data(),
			sqlQuery.lastError().text().data());
		return false;
	}
	else if (sqlQuery.numRowsAffected() <= 0) {
        xxErr("can not find anything, failure to delete values. tableName:%s", tableName.toLocal8Bit().data());
	}
	else {
        //xxInfo("delete values succeed. tableName:%s", tableName.toLocal8Bit().data());
	}
	return true;
}
bool SqlDatabaseManager::setValues2Table(const QMap<QString,QVariant> &values, const QMap<QString,QVariant> &equalTo, const QString &tableName){
    if(!_isOpen || values.isEmpty() || equalTo.isEmpty() || tableName.isEmpty()){
        xxErr("database not open or invalid arg.");
        return false;
    }

    QString vString;
    for (auto iter=values.constBegin(); iter!=values.constEnd(); iter++) {
        vString += iter.key();
        vString += "=?";
        if(iter+1!=values.constEnd()){
            vString +=",";
        }
    }

    QString eqString;
    for (auto iter=equalTo.constBegin(); iter!=equalTo.constEnd(); iter++) {
        eqString += iter.key();
        eqString += "=?";
        if(iter+1!=equalTo.constEnd()){
            eqString += " AND ";
        }
    }

    QString sqlString = QString("UPDATE %1 SET %2 WHERE %3;").arg(tableName).arg(vString).arg(eqString);
    QSqlQuery sqlQuery(_database);
    sqlQuery.prepare(sqlString);
    for (auto iter=values.constBegin(); iter!=values.constEnd(); iter++) {
        sqlQuery.addBindValue(iter.value());
    }
    for (auto iter=equalTo.constBegin(); iter!=equalTo.constEnd(); iter++) {
        sqlQuery.addBindValue(iter.value());
    }

    if(!sqlQuery.exec()){
        xxErr("set values failure. tableName:%s sql:%s error:%s",
                tableName.toLocal8Bit().data(),
                sqlString.toLocal8Bit().data(),
                sqlQuery.lastError().text().data());
        return false;
    }
    else if(sqlQuery.numRowsAffected()<=0){
        xxErr("can not find anything, failure to set values. tableName:%s", tableName.toLocal8Bit().data());
    }
    else{
        //xxInfo("set values succeed. tableName:%s", tableName.toLocal8Bit().data());
    }
    return true;
}
bool SqlDatabaseManager::addMap2Table(const QVariantMap &values, const QString &tableName){
    if(!_isOpen || values.isEmpty() || tableName.isEmpty()){
        xxErr("database not open or invalid arg.");
        return false;
    }

    QString kString, vString;
    for (auto iter=values.constBegin(); iter!=values.constEnd(); iter++) {
        kString += iter.key();
        vString += "?";
        if(iter+1!=values.constEnd()){
            kString += ",";
            vString += ",";
        }
    }

    QString sqlString = QString("insert into %1 (%2) values (%3);").arg(tableName).arg(kString).arg(vString);
    QSqlQuery sqlQuery(_database);
    sqlQuery.prepare(sqlString);
    for (auto iter=values.constBegin(); iter!=values.constEnd(); iter++) {
        sqlQuery.addBindValue(iter.value());
    }

    if(!sqlQuery.exec()){
        xxErr("add map failure. tableName:%s sql:%s error:%s",
                tableName.toLocal8Bit().data(),
                sqlString.toLocal8Bit().data(),
                sqlQuery.lastError().text().data());
        return false;
    }

    //XXDebug("add map succeed. tableName:%s", tableName.toLocal8Bit().data());
    return true;
}
bool SqlDatabaseManager::addMaps2Table(const QList<QVariantMap> &maps, const QString &tableName){
    _database.driver()->beginTransaction();
    bool ret = false;
    foreach(auto map,maps){
        ret = addMap2Table(map,tableName);
        if(!ret){
            break;
        }
    }
    if(ret){
        _database.driver()->commitTransaction();
    }
    else{
        _database.driver()->rollbackTransaction();
    }
    return ret;
}

QList<QVariantMap> SqlDatabaseManager::getMapFromTable(const QString &tableName, const QVariantMap &equelTo, const QStringList &orderBy, bool ASC){
    QList<QVariantMap> list;
    if(!_isOpen || tableName.isEmpty()){
        xxErr("database not open or invalid arg.");
        return list;
    }

    QString eqString;
    for (auto iter=equelTo.constBegin(); iter!=equelTo.constEnd(); iter++) {
        eqString += iter.key();
        eqString += "=?";
        if(iter+1!=equelTo.constEnd()){
            eqString += " AND ";
        }
    }

    QString orderString = "";
    for (auto iter=orderBy.constBegin(); iter!=orderBy.constEnd(); iter++) {
        if(orderBy.constBegin() == iter){
            orderString = "ORDER BY ";
        }
        orderString += *iter;
        if(iter+1!=orderBy.constEnd()){
            eqString += ", ";
        }
    }
    if(!orderBy.isEmpty()){
        orderString += ASC?" ASC":" DESC";
    }

    QString sqlString;
    if(eqString.isEmpty()){
        if(orderBy.isEmpty()){
            sqlString = QString("SELECT * FROM %1;").arg(tableName);
        }
        else{
            sqlString = QString("SELECT * FROM %1 %2;").arg(tableName).arg(orderString);
        }
    }
    else{
        if(orderBy.isEmpty()){
            sqlString = QString("SELECT * FROM %1 WHERE %2;").arg(tableName).arg(eqString);
        }
        else{
            sqlString = QString("SELECT * FROM %1 WHERE %2 %3;").arg(tableName).arg(eqString).arg(orderString);
        }
    }
    QSqlQuery sqlQuery(_database);
    sqlQuery.prepare(sqlString);

    for (auto iter=equelTo.constBegin(); iter!=equelTo.constEnd(); iter++) {
        sqlQuery.addBindValue(iter.value());
    }

    if(!sqlQuery.exec()){
        xxErr("failure to get map. tableName:%s sql:%s error:%s",
                tableName.toLocal8Bit().data(),
                sqlString.toLocal8Bit().data(),
                sqlQuery.lastError().text().data());
    }
    else{
        while(sqlQuery.next()){
           QSqlRecord record = sqlQuery.record();
           int count = record.count();
           QVariantMap kvMap;
           for (int index=0; index<count; index++) {
               kvMap[record.fieldName(index)] = record.value(index);
           }
           list << kvMap;
        }
    }

    return list;
}

QList<QVariantMap> SqlDatabaseManager::getMapFromTableRelation(const QString &tableName, const QMap<QString,QPair<QVariant,Relation>> &relaTo, const QStringList &orderBy, bool ASC){
    QList<QVariantMap> list;
    if(!_isOpen || tableName.isEmpty()){
        xxErr("database not open or invalid arg.");
        return list;
    }

    QString relaString;
    for (auto iter=relaTo.constBegin(); iter!=relaTo.constEnd(); iter++) {
        relaString += iter.key();
        switch (iter.value().second) {
            case Equal:             relaString +="=?"; break;
            case MoreThan:          relaString +=">?"; break;
            case MoreThanAndEqual:  relaString +=">=?"; break;
            case LessThan:          relaString +="<?"; break;
            case LessThanAndEqual:  relaString +="<=?"; break;
            case NotEqual:          relaString +="!=?"; break;
            //default: return list;
        }
        if(iter+1!=relaTo.constEnd()){
            relaString += " AND ";
        }
    }

    QString orderString = "";
    for (auto iter=orderBy.constBegin(); iter!=orderBy.constEnd(); iter++) {
        if(orderBy.constBegin() == iter){
            orderString = "ORDER BY ";
        }
        orderString += *iter;
        if(iter+1!=orderBy.constEnd()){
            orderString += ", ";
        }
    }
    if(!orderBy.isEmpty()){
        orderString += ASC?" ASC":" DESC";
    }

    QString sqlString;
    if(relaString.isEmpty()){
        if(orderBy.isEmpty()){
            sqlString = QString("SELECT * FROM %1;").arg(tableName);
        }
        else{
            sqlString = QString("SELECT * FROM %1 %2;").arg(tableName).arg(orderString);
        }
    }
    else{
        if(orderBy.isEmpty()){
            sqlString = QString("SELECT * FROM %1 WHERE %2;").arg(tableName).arg(relaString);
        }
        else{
            sqlString = QString("SELECT * FROM %1 WHERE %2 %3;").arg(tableName).arg(relaString).arg(orderString);
        }
    }
    QSqlQuery sqlQuery(_database);
    sqlQuery.prepare(sqlString);

    for (auto iter=relaTo.constBegin(); iter!=relaTo.constEnd(); iter++) {
        sqlQuery.addBindValue(iter.value().first);
    }

    if(!sqlQuery.exec()){
        xxErr("failure to get map. tableName:%s sql:%s error:%s",
                tableName.toLocal8Bit().data(),
                sqlString.toLocal8Bit().data(),
                sqlQuery.lastError().text().data());
    }
    else{
        while(sqlQuery.next()){
           QSqlRecord record = sqlQuery.record();
           int count = record.count();
           QVariantMap kvMap;
           for (int index=0; index<count; index++) {
               kvMap[record.fieldName(index)] = record.value(index);
           }
           list << kvMap;
        }
    }

    return list;
}

int SqlDatabaseManager::getValueMaxFromTable(const QString &tableName, const QString &key) {
	if (!_isOpen || tableName.isEmpty()) {
        xxErr("database not open or invalid arg.");
		return -1;
	}

	QString sqlString = QString("SELECT MAX(%1) FROM %2;").arg(key).arg(tableName);
	QSqlQuery sqlQuery(_database);
	if (!sqlQuery.exec(sqlString)) {
        xxErr("failure to get key max. tableName:%s sql:%s error:%s",
			tableName.toLocal8Bit().data(),
			sqlString.toLocal8Bit().data(),
			sqlQuery.lastError().text().data());
		return -1;
	}

	sqlQuery.next();
	return sqlQuery.value(0).toInt();
}
void SqlDatabaseManager::updateVersion(uint version){
    if (!_isOpen) {
        xxErr("database not open.");
        return;
    }

    QString sqlString = QString("PRAGMA user_version=%1;").arg(version);
    QSqlQuery sqlQuery(_database);
    if (!sqlQuery.exec(sqlString)) {
        xxErr("failure to set user_version. version:%d",version);
    }
}
int SqlDatabaseManager::getVersion(){
    if (!_isOpen) {
        xxErr("database not open.");
        return -1;
    }
    QSqlQuery sqlQuery(_database);
    if (!sqlQuery.exec("PRAGMA user_version")) {
        xxErr("failure to get user_version. error:%s",sqlQuery.lastError().text().data());
        return -1;
    }

    sqlQuery.next();
    return sqlQuery.value(0).toInt();
}
bool SqlDatabaseManager::isNewest(){
    return static_cast<int>(getGlobalVersion()) == getVersion();
}

bool SqlDatabaseManager::close(){
    if(!_isOpen){
        xxErr("none database is opening.");
        return false;
    }

    _isOpen = false;

    QString connection = _database.connectionName();
    _database.close();
    _database = QSqlDatabase();
    _database.removeDatabase(connection);
    return true;
}

QString SqlDatabaseManager::getTableNameAtMetaObject(QMetaObject metaObject){
    int index = metaObject.indexOfClassInfo(kTableName);
    if(index<0){
        return "";
    }
    return metaObject.classInfo(index).value();
}
